<?php
// 数据库相关库

/*查询数据库符合条件的记录条数*/
function sqlNum($table, $where): int|string
{
    $con = $GLOBALS['con'];
    if (empty($where)) {
        $where = "";
    } elseif (strtolower(substr(trim($where), 0, 3)) == "and") {
        $where = " where 1=1 " . $where;
    } else {
        $where = " where " . $where;
    }
    $sql = "select * from " . $table . $where;
    if ($query = mysqli_query($con, $sql)) {
        $num = mysqli_num_rows($query);
        if ($num > 0) {
        } else {
            $num = 0; //数据库查询语句有误时$num为空
        }
    } else {
        $num = "数据查询失败：" . $sql;
    }
    return $num;
}

/*查询数据库表内容
$data = array(
    "table" =>*"需要查询的数据库表名",
    "field" => "需要返回的字段，未设置时返回所有字段",
    "sum" => "给某个字段求和",
    "where" => "查询条件，未设置时返回所有记录",
    "single" => "唯一数组查询",
);
*/
function find(array $data): float|array|string
{
    $con = $GLOBALS['con'];
    if (empty($data['table'])) {
        $result = "数据库表名称为空";
    } else {
        //返回的字段
        if (empty($data['field'])) {
            if (isset($data['sum'])) {
                $data['field'] = " ifnull(sum(" . $data['sum'] . "),0) as " . $data['sum'];
            } else {
                $data['field'] = "*";
            }
        }
        //查询条件
        $where = '';
        if (isset($data['where']) && $data['where']) {
            $where = $data['where'];
        } elseif (isset($data['single'])) {
            $where = $data['single'];
        }

        if (!empty($where)) {
            $temp = strtolower(trim($where));
            if (str_starts_with($temp, "and") or str_starts_with($temp, "order by")) {
                $where = " 1=1 " . $where;
            }
            $where = $where ? (" where " . $where) : '';
        }
        //执行
        $result['array'] = [];
        $result['sql']   = "select " . $data['field'] . " from " . $data['table'] . $where;
        if (isset($data['debug']) && $data['debug'] === true) {
            return $result['sql'] . ';' . PHP_EOL;
        }
        $query = mysqli_query($con, $result['sql']);
        if (!$query) {
            $result = "查询失败：" . $result['sql'];
        } elseif (isset($data['sum'])) {
            $array  = mysqli_fetch_assoc($query);
            $result = floatval((empty($result)) ? 0 : $array[$data['sum']]);
        } elseif (isset($data['single'])) {
            $result = (array)mysqli_fetch_assoc($query);
            if (isset($result['price'])) {
                $result['price'] = floatval($result['price']);
            }
        } else {
            $result['num'] = mysqli_num_rows($query);
            if ($result['num'] == 0) {
                $result['warn'] = "一条记录都没有";
            } else {
                while ($array = mysqli_fetch_assoc($query)) {
                    if (isset($array['price'])) {
                        $array['price'] = floatval($array['price']);
                    }
                    $result['array'][] = $array;
                }
            }
        }
    }
    //返回
    return $result;
}

/**
 * 查询函数
 *
 * @param string $table
 * @param string $where 条件
 * @param string $field 字段
 * @return array|string
 */
function query(string $table, string $where, string $field = '*'): array|string
{
    $info = find([
        'field'  => $field,
        "table"  => $table,
        "single" => $where . " limit 1",
    ]);
    return $info ?: setDefault($table, $field);
}

/**
 * 解决返回数组不存在key的问题
 *
 * @param string $table
 * @param string $field
 * @return array
 */
function setDefault(string $table, $field): array
{
    global $conf;
    $sql    = "SELECT COLUMN_NAME FROM information_schema.columns WHERE table_schema = '{$conf['databaseName']}' AND table_name = '$table' ";
    $result = sqlFind($sql);
    $array  = [];
    foreach ($result as $val) {
        if (isset($val['COLUMN_NAME']) && ($field === '*' || str_contains($field, $val['COLUMN_NAME']))) {
            $array[$val['COLUMN_NAME']] = '';
        }
    }
    return $array;
}


/**
 * 新增一条数据库记录
 *
 * @param array $data
 *   $data = array(
 *   "table" => "需要更新的数据库表名",
 *   "field" => "需要更新的字段数组，键名为字段名称，键值为字段值",
 *   );
 * @return string
 */
function insert(array $data): string
{
    if (empty($data['table'])) {
        $html = "数据库表名称为空";
    } elseif (empty($data['field'])) {
        $html = "数据库新增字段为空";
    } else {
        $dataType = dataType($data['table']);
        $field    = $text = $space = '';
        foreach ($data['field'] as $key => $value) {
            $field .= $space . "`$key`";
            $value = setNull($key, $value, $dataType);
            if (is_null($value)) {
                $text .= $space . "null";
            } else {
                $text .= $space . "'$value'";
            }
            $space = ', ';
        }
        $data['table'] = str_contains($data['table'], '`') ? $data['table'] : " `{$data['table']}` ";
        $sql           = " INSERT INTO {$data['table']} ($field) VALUES ($text)";
        if (isset($data['debug']) && $data['debug'] === true) {
            return $sql . ';' . PHP_EOL;
        }
        $bool = sqlExec($sql);
        $html = ($bool === true) ? '新增成功' : '新增失败：' . mysqli_error_msg($data['table']);
    }
    return $html;
}


/**
 * 更新数据库中某条记录的数据
 *
 * @param array $data
 *   $data = array(
 *   "table" => "需要更新的数据库表名",
 *   "field" => "需要更新的字段数组，键名为字段名称，键值为字段值",
 *   "where" => "需要更新的数据库表记录查询条件",
 *   );
 * @return string
 * @throws Exception
 */
function update(array $data): string
{
    if (empty($data['table'])) {
        $html = "数据库表名称为空";
    } elseif (empty($data['field'])) {
        $html = "数据库更新字段为空";
    } elseif (empty($data['where'])) {
        $html = "数据库查询语句为空";
    } else {
        $field    = $link = '';
        $dataType = dataType($data['table']);
        foreach ($data['field'] as $key => $value) {
            $value = setNull($key, $value, $dataType);
            if (is_null($value)) {
                $field .= $link . "`$key`" . " = null ";
            } else {
                $field .= $link . "`$key`" . " = '" . $value . "'";
            }
            $link = ", ";
        }
        $data['table'] = str_contains($data['table'], '`') ? $data['table'] : " `{$data['table']}` ";
        $sql           = " UPDATE {$data['table']} SET $field WHERE {$data['where']}";
        if (isset($data['debug']) && $data['debug'] === true) {
            return $sql . ';' . PHP_EOL;
        }
        $bool = sqlExec($sql);
        $html = ($bool === true) ? '更新成功' : '更新失败：' . mysqli_error_msg($data['table']);
    }
    return $html;
}

/**
 * 翻译3种常见数据库错误提示成中文，其余原样返回
 *
 * @param string $table 数据库表名
 * @return string
 * @throws Exception
 */
function mysqli_error_msg(string $table): string
{
    global $con, $conf;
    $error  = mysqli_error($con);
    $errMsg = $error;
    $table  = str_contains($table, '`') ? trim(str_replace('`', '', $table)) : $table;
    //Data too long for column 'name' at row 1
    //Out of range value for column 'list' at row 1
    //Incorrect datetime value: '2022-0119' for column 'updateTime' at row 1
    if (!empty($error)) {
        $columnSql = "SELECT COLUMN_COMMENT as text,CHARACTER_MAXIMUM_LENGTH as longth FROM information_schema.columns WHERE table_schema = '{$conf['databaseName']}' AND table_name = '$table' ";
        if (str_contains($error, 'too long')) {
            $columnErr = explode("'", $error)[1];
            $columnSql .= " AND COLUMN_NAME='$columnErr' ";
            $result    = sqlFind($columnSql)[0];
            $errMsg    = "【{$result['text']}】内容长度超出设定值：{$result['longth']} ";
        } elseif (str_contains($error, 'range value')) {
            $columnErr = explode("'", $error)[1];
            $columnSql .= " AND COLUMN_NAME='$columnErr' ";
            $result    = sqlFind($columnSql)[0];
            $errMsg    = "【{$result['text']}】数值超出设定范围 ";
        } elseif (str_contains($error, 'Incorrect date')) {
            $columnErr = explode("'", $error)[3];
            $columnSql .= " AND COLUMN_NAME='$columnErr' ";
            $result    = sqlFind($columnSql)[0];
            $errMsg    = "【{$result['text']}】日期格式有误 ";
        }
    }
    return formSub($errMsg);
}

function replace($string, $array = ['`', ' '])
{
    return str_replace($array, '', $string);
}

/**
 * 获取表中字段的数据类型
 *
 * @param $table
 * @return array
 * @throws Exception
 */
function dataType($table): array
{
    global $conf;
    $data  = [];
    $array = explode('`.`', $table);
    if (count($array) == 1) {
        $sql = " SELECT `COLUMN_NAME`, `DATA_TYPE`  FROM `information_schema`.`COLUMNS` WHERE `TABLE_NAME`='$table' AND `TABLE_SCHEMA` = '{$conf['databaseName']}' ; ";
    } else {
        $TABLE_SCHEMA = replace($array[0]);
        $TABLE_NAME   = replace($array[1]);
        $sql          = " SELECT `COLUMN_NAME`, `DATA_TYPE`  FROM `information_schema`.`COLUMNS` WHERE `TABLE_NAME`='$TABLE_NAME' AND `TABLE_SCHEMA` = '$TABLE_SCHEMA' ; ";
    }
    $result = sqlFind($sql);
    foreach ($result as $array) {
        $data[$array['COLUMN_NAME']] = $array['DATA_TYPE'];
    }
    return $data;
}

function setNull(string $key, $value, $dataType)
{
    $key = $dataType[$key];
    return match ($key) {
        'datetime', 'date', 'json', 'text'    => $value ?: null,
        'int', 'bigint', 'tinyint', 'decimal' => $value ?: 0,
        default                               => $value ?? '',
    };
}


/**
 * 删除数据库记录
 *
 * @param array $data
 *    $data = array(
 *    "table" =>*"数据库表名",
 *    "where" =>*"删除条件",
 *    );
 * @return string
 */
function delete(array $data): string
{
    if (empty($data['table'])) {
        $html = "数据库表名称为空";
    } elseif (empty($data['where'])) {
        $html = "删除条件为空";
    } else {
        $sql = "DELETE FROM {$data['table']} WHERE {$data['where']}";
        if (isset($data['debug']) && $data['debug'] === true) {
            return $sql . ';' . PHP_EOL;
        }
        $bool = sqlExec($sql);
        $html = ($bool === true) ? '删除成功' : '删除失败';
    }
    return $html;
}

/**
 * 执行sql 语句
 *
 * @param string $sql
 * @param bool $getRes 取结果
 * @return bool|mysqli_result
 * @throws Exception
 */
function sqlExec(string $sql, bool $getRes = false): mysqli_result|bool
{
    $con = $GLOBALS['con'];
    $res = mysqli_query($con, $sql);
    if (!$res) {
        throw new Exception('MySQL 错误: ' . mysqli_error($con) . '错误代码为：' . PHP_EOL . $sql . PHP_EOL);
    }
    if ($getRes === false) {
        return (bool)$res;
    } else {
        return $res;
    }
}

/**
 * SQL 语句查找
 *
 * @param string $sql
 * @return array
 * @throws Exception
 */
function sqlFind(string $sql): array
{
    $res   = sqlExec($sql, true);
    $array = [];
    if ($res) {
        while ($data = mysqli_fetch_array($res, MYSQLI_ASSOC)) {
            $array[] = $data;
        }
        return $array;
    }
    return $array;
}

/**
 * 生成IN SQL 语句
 */
function whereIn($array): string
{
    $total = sizeof($array);
    $i     = 1;
    $str   = '';
    foreach ($array as $val) {
        $str .= " '$val' ";
        if ($i != $total) {
            $str .= ',';
        }
        $i++;
    }
    return $str;
}

/**
 * 生成 Where 支持多次调用
 * condition 附加 self::$where
 */
function generateWhere($where)
{
    $str = '';
    if (is_array($where)) {
        $total = sizeof($where);
        $i     = 1;
        foreach ($where as $key => $val) {
            if (str_contains($key, '.')) {
                $str .= "$key = '$val' ";
            } else {
                $str .= "`$key` = '$val' ";
            }
            if ($i != $total) {
                $str .= ' AND ';
            }
            $i++;
        }
    } else {
        $str = $where;
    }
    return $str;
}

/**
 * 按条件统计表数量
 *
 * @param array $data
 * @return int|float|string 结果
 */
function total(array $data): int|float|string
{
    $where = '';
    if (empty($data['sql'])) {
        $data['sql'] = "SELECT IFNULL(count(*), 0) AS total FROM {$data['table']} WHERE ";
    }
    // 判断是否含有where
    if (stripos($data['sql'], 'WHERE') === false) {
        $data['sql'] .= ' WHERE ';
    }
    // 判断是否含有 1=1
    if (stripos($data['sql'], '1=1') === false) {
        $temp = explode('WHERE', $data['sql']);
        // 不存在and 且 不存在 =
        if (stripos(end($temp), 'AND') === false && stripos(end($temp), '=') === false) {
            $data['sql'] .= ' 1=1 ';
        }
    }
    // 判断是否带条件
    if (!empty($data['where'])) {
        // 条件是数组
        if (is_array($data['where'])) {
            $data['where'] = generateWhere($data['where']);
        }
        // 如果条件不存在 and
        $before = substr(strtolower(trim($data['where'])), 0, 3);
        if (stripos($before, 'and') === false) {
            $endStr = substr(strtolower(trim($data['sql'])), -5, 5);
            $where  = $endStr == 'where' ? (' ' . $data['where']) : (' AND ' . $data['where']);
        } else {
            $where = $data['where'];
        }
    }
    $data['sql'] .= !empty($data['group']) ? $where . $data['group'] : $where;
    if (isset($data['debug']) && $data['debug'] === true) {
        return $data['sql'] . ';' . PHP_EOL;
    }
    $res = mysqli_fetch_assoc(sqlExec($data['sql'], true));
    return floatval($res['total']);
}

/**
 * @param $con
 * @param $sql
 * @param $debug
 * @return array|int[]
 */
function executeSQL($con, $sql, $debug)
{
    if ($debug) {
        echo $sql, PHP_EOL;
        return ['success' => 0, 'fail' => 0];
    }
    try {
        $result = mysqli_query($con, $sql);
        return ['success' => mysqli_affected_rows($con), 'fail' => !$result];
    } catch (\Exception $e) {
        return ['success' => 0, 'fail' => 1];
    }
}

/**
 * 构建插入 SQL 语句
 */
function buildInsertSQL($tableName, $columns, $batchData, $dataType)
{
    $sql       = "INSERT INTO `$tableName` (`" . implode("`, `", $columns) . "`) VALUES ";
    $valuesArr = [];
    foreach ($batchData as $row) {
        $values = [];
        foreach ($row as $key => $value) {
            $value    = setNull($key, $value, $dataType);
            $values[] = is_null($value) ? "NULL" : "'" . $value . "'";
            //$values[] = is_null($value) ? "NULL" : "'" . addslashes($value) . "'";
        }
        $valuesArr[] = "(" . implode(", ", $values) . ")";
    }
    return $sql . implode(", ", $valuesArr);
}

/**
 * 构建更新 SQL 语句
 */
function buildUpdateSQL($tableName, $batchData, $batchWhere, $dataType)
{
    $sqlParts = [];
    foreach ($batchData as $row) {
        $updates = [];
        foreach ($row as $key => $value) {
            $value = setNull($key, $value, $dataType);
            //$value     = is_null($value) ? "NULL" : "'" . addslashes($value) . "'";
            $value     = is_null($value) ? "NULL" : "'" . $value . "'";
            $updates[] = "`$key` = $value";
        }
        $sqlParts[] = " UPDATE `$tableName` SET " . implode(', ', $updates) . $batchWhere;
    }

    return implode('; ', $sqlParts);
}